class CreateLanguages < ActiveRecord::Migration
  def self.up
    create_table :languages do |t|
      t.string :code
      t.string :name
      t.integer :indexed_records
    end
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('en', 'English', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'en'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('es', 'Spanish', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'es'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('de', 'German', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'de'));"
#    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('el', 'Greek', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'el'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('fr', 'French', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'fr'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('ja', 'Japanese', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'ja'));"
#    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('ko', 'Korean', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'ko'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('nl', 'Dutch', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'nl'));"
#    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('pt', 'Portuguese', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'pt'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('ru', 'Russian', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'ru'));"
#    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('th', 'Thai', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'th'));"
    execute "INSERT INTO languages (code, name, indexed_records) VALUES ('zh', 'Chinese', (SELECT count(*) FROM entries WHERE substring(language,1,2) = 'zh'));"
  end

  def self.down
    drop_table :languages
  end
end
